package com.origin.onlinesys.common.services;

import org.apache.poi.hssf.usermodel.*;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2015/3/10 0010.
 */
public class PoiUtils {
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private DecimalFormat df = new DecimalFormat("#.##");
    public List<List<String>> analyzeXls(int startRow,String  path){
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> value = null;

        InputStream is = null;
        try {
            is = new FileInputStream(path);
            HSSFWorkbook wb = new HSSFWorkbook(is);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row = sheet.getRow(0);
            int with = row.getPhysicalNumberOfCells();
            int height = sheet.getLastRowNum();

            HSSFRow dataRow = null;
            for(int j = startRow;j <= height;j++){
                dataRow = sheet.getRow(j);
                value = new ArrayList<String>();
                for(int i = 0;i<with;i++){
                    value.add(i, this.getCellValue(dataRow.getCell(i)));
                }
                list.add(value);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(is != null){
                try {is.close();
                }catch (Exception e) {
                }
            }
        }
        return list;
    }

    /**
     * 得到每个单元格的值
     * @param cell
     * @return
     */
    private String getCellValue(HSSFCell cell){
        if(cell==null)return null;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //如果是date类型则 ，获取该cell的date值
                    return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }else{//纯数字
                    return df.format(cell.getNumericCellValue()).trim();
                }
            case HSSFCell.CELL_TYPE_STRING: // 字符串型
                return  cell.getRichStringCellValue().toString().trim();
            case HSSFCell.CELL_TYPE_FORMULA://公式型
                String value = String.valueOf(cell.getNumericCellValue()).trim();
                //读公式计算值
                if(value.equals("NaN")){//如果获取的数据值为非法值,则转换为获取字符串
                    value = cell.getRichStringCellValue().toString().trim();
                }
                return value;
            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                return  cell.getBooleanCellValue()+"".trim();
            case HSSFCell.CELL_TYPE_BLANK: // 空值
                return  null;
            case HSSFCell.CELL_TYPE_ERROR: // 故障
                return  null;
            default:
                return cell.getRichStringCellValue().toString().trim();
        }
    }
}
